﻿using System;
using System.Collections.Generic;
using System.Text;
using ExtBusiness.Struct;
using System.Data;
using System.Collections;
using WF_Business;

namespace ExtBusiness.User
{
    public class UserNew
    {

        /// <summary>
        /// 获取所有用户信息
        /// </summary>
        /// <returns></returns>
        /// <!--
        /// 创建人  : LinJian
        /// 创建时间: 2007年5月23日
        /// 修改人: Ly
        /// 修改时间: 2008-05-13
        /// 修改事由:按照姓名查询时，如果用户组中配置有人员，所属用户靠前排列并勾选。
        /// -->
        public static List<UserStruct> GetAllUserTemp(string groupId, string userName)
        {
            string strUserName = userName.Trim();
            string strSql = "select m.* from ST_USER m where 1=1 ";
            
            if (!String.IsNullOrEmpty(groupId))//有用户组的话，把用户组所属的用户排前面
            {
                strSql += string.Format(@" and m.UserId in 
                    (SELECT UserId FROM ST_USER_GROUP WHERE GId='{0}')
                     UNION ALL SELECT m.* FROM ST_USER m  WHERE m.UserId NOT IN 
                    (SELECT UserId FROM ST_USER_GROUP WHERE GId='{0}')", groupId);
            }
            if (!String.IsNullOrEmpty(strUserName))
            {
                strSql += " and m.User_Name LIKE '%" + strUserName + "%'";
            }
            DataTable dtPost;
            SysParams.OAConnection().RunSql(strSql, out dtPost);

            List<UserStruct> lstUser = new List<UserStruct>();
            foreach (DataRow drTemp in dtPost.Rows)
            {
                UserStruct user = new UserStruct();
                user.UserId = drTemp["USERID"].ToString();
                user.UserName = drTemp["LOGIN_NAME"].ToString();
                user.UserRealName = drTemp["USER_NAME"].ToString();
                user.UserPass = drTemp["PASSWORD"].ToString();
                user.UserOderbyid = drTemp["ORDERBYID"].ToString();
                user.UserInvalid = drTemp["INVALID"].ToString();
                lstUser.Add(user);
            }

            return lstUser;
        }

        /// <summary>
        /// 根据部门、角色、用户获取
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="userName"></param>
        /// <param name="departName"></param>
        /// <returns></returns>
        public static List<UserStruct> GetAllUserTemp(String groupId, String userName, String departName)
        {
            string strUserName = userName.Trim();
            string strSql = "SELECT m.* FROM ST_USER m where 1 = 1 ";

            if (!String.IsNullOrEmpty(departName))
            {
                strSql = string.Format(@"select m.* from ST_USER m,st_department t,st_user_department b 
                    where t.depart_name='{0}' and m.userid=b.userid  and b.order_id=t.departid ", departName);
            }
            if (!String.IsNullOrEmpty(groupId))
            {
                strSql += string.Format(" and m.UserId in(SELECT UserId FROM ST_USER_GROUP WHERE GId='{0}')", groupId);
            }
            if (!String.IsNullOrEmpty(strUserName))
            {
                strSql += string.Format(" and m.User_Name LIKE '%{0}%'", strUserName);
            }

            strSql += @"  order by m.orderbyid";
            DataTable dtPost;
            SysParams.OAConnection().RunSql(strSql, out dtPost);

            List<UserStruct> lstUser = new List<UserStruct>();
            foreach (DataRow drTemp in dtPost.Rows)
            {
                UserStruct user = new UserStruct();
                user.UserId = drTemp["USERID"].ToString();
                user.UserName = drTemp["LOGIN_NAME"].ToString();
                user.UserRealName = drTemp["USER_NAME"].ToString();
                user.UserPass = drTemp["PASSWORD"].ToString();
                user.UserOderbyid = drTemp["ORDERBYID"].ToString();
                user.UserInvalid = drTemp["INVALID"].ToString();
                lstUser.Add(user);
            }

            return lstUser;
        }

        /// <summary>
        /// 验证系统名
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool CheckSysName(string name)
        {
            try
            {
                string strSql = string.Format(@"select UserTypeCode from sys_usertype where sysname='{0}'", name);
                string result = SysParams.OAConnection().GetValue(strSql);

                if (!string.IsNullOrEmpty(result))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
        }


        /// <summary>
        /// Inserts the type of the user.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <returns></returns>
        public bool InsertUserType(Hashtable table)
        {
            string strSql = string.Format("select USERTYPECODE from SYS_USERTYPE where SYSCODE='{0}'",
                table["SYSCODE"]);
            string strResult = SysParams.OAConnection().GetValue(strSql);

            if (!string.IsNullOrEmpty(strResult))
            {
                strSql = string.Format(@"Insert into SYS_USERTYPE(SYSCODE,USERTYPENAME,USERTYPECODE,
                        SYSNAME) values('{0}','{1}',(select max(usertypecode)+1 from SYS_USERTYPE ),'{2}')", table["SYSCODE"],
                    table["USERTYPENAME"], table["SYSNAME"]);
            }
            else
            {
                strSql = string.Format(@"update SYS_USERTYPE set USERTYPENAME='{1}',
                        USERTYPECODE=(select max(usertypecode)+1 from SYS_USERTYPE ) where SYSCODE='{0}'",
                    table["SYSCODE"], table["USERTYPENAME"]);
            }

            try
            {
                SysParams.OAConnection().RunSql(strSql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 插入系统名
        /// </summary>
        /// <param name="strSysName"></param>
        /// <returns></returns>
        public bool InsertSysName(string strSysName)
        {
            try
            {
                string strSql = string.Format(@"Insert into SYS_USERTYPE(SYSCODE,SYSNAME) 
                values((select to_char(max(SYSCODE)+1,'000') from SYS_USERTYPE),'{0}')",
                    strSysName);

                SysParams.OAConnection().RunSql(strSql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// Gets the name of the sys.
        /// </summary>
        /// <returns></returns>
        public DataTable GetSysName()
        {
            string strSql = @"select distinct SYSNAME as 系统名称 from sys_usertype";
            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql,out dtTemp);
            return  dtTemp;
        }

        /// <summary>
        /// Gets the name of the sys code by sys.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <returns></returns>
        public string GetSysCodeBySysName(string name)
        {
            string strSql = string.Format("select SysCode from sys_usertype where sysname='{0}'",name);
            return SysParams.OAConnection().GetValue(strSql);
        }

        #region 根据用户id或用户名查密码
        /// <summary>
        /// 根据用户id或用户名查密码
        /// </summary>
        /// <param name="strUserId"></param>
        /// <param name="strUserName"></param>
        /// <returns></returns>
        public static DataTable QueryPassByUserInfo(string strUserId, string strUserName)
        {
            string strSql = @"select id,userid,username,query_pass from sys_user where 1=1";

            if (!string.IsNullOrEmpty(strUserId))
            {
                strSql += string.Format(" and userid='{0}'", strUserId.ToUpper());
            }

            if (!string.IsNullOrEmpty(strUserName))
            {
                strSql += string.Format(" and username like '%{0}%'", strUserName);
            }
            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }
        #endregion
    }
}
